import csv

import pymysql

conn = pymysql.connect(host='127.0.0.1',
                       port=3306,
                       user='root',
                       password='root',
                       db='test1',
                       charset='utf8')


def insert(l, conn):
    sql = 'INSERT INTO train2 VALUES(%s,%s,%s,%s,%s)'

    with  conn.cursor() as cur:
        try:
            cur.execute(sql,l)
            conn.commit()
        except Exception as e :
            print(e)


def write():
    f = csv.reader(open(r'C:\Users\Lenovo\Desktop\datagrand_0517\train.csv', 'r'))
    list1=list()
    num = 0
    for i in f:
        num += 1
        if num == 1:
            continue

        user_id, item_id, cate_id, action_type, action_time = i
        if len(user_id)>19 or len(item_id)>19 or len(cate_id)>19 or len(action_type)>19:
            continue
        action_time = int(action_time)

        list1.append((user_id, item_id, cate_id, action_type, action_time))
        if len(list1)==200000:
            for l in list1:
                print(l)
            break
            insert(list1, conn)
            list1.clear()
            print(num)
    print(num)

def test():
    l=[
        ('13475863', '526853', '1_6', 'view', 1487179638),
        ('16203611', '530095', '1_14', 'view', 1487179638),
        ('4887693', '535130', '1_3', 'view', 1487179638)
    ]

    sql = 'INSERT INTO train2 VALUES(%s,%s,%s,%s,%s)'
    with  conn.cursor() as cur:
        try:
            cur.execute(sql,l)
            conn.commit()
        except Exception as e :
            print(e)



if __name__ == '__main__':
    test()
